package com.termine.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import javax.faces.bean.ApplicationScoped;
import javax.faces.bean.ManagedBean;

import com.termine.db.Connector;
import com.termine.entity.Availability;
import com.termine.entity.PersonalData;

@ManagedBean
@ApplicationScoped
public class AvailabilityDAO {

	public void updateAvailability(Availability availability) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "update AVAILABILITY set AV_START = ?, AV_END = ? where AV_ID = ?";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setTimestamp(1, new Timestamp(availability.getStart().getTime()));
			statement.setTimestamp(2, new Timestamp(availability.getEnd().getTime()));
			statement.setInt(3, availability.getAvailability_id());
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}

	public void addAvailability(Availability availability, PersonalData personalData) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "insert into AVAILABILITY (P_ID, AV_START,AV_END) values (?,?,?)";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, personalData.getProvider_id());
			statement.setTimestamp(2, new Timestamp(availability.getStart().getTime()));
			statement.setTimestamp(3, new Timestamp(availability.getEnd().getTime()));
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}

	public void deleteAvailability(Availability availability) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "delete from AVAILABILITY where AV_ID = ?";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, availability.getAvailability_id());
			statement.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}

	public List<Availability> getAllAvailabilitiesForProvider(
			PersonalData personalData) throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		String statementString = "select * from AVAILABILITY where P_ID = ?";
		try {
			connection = Connector.getConnection();
			statement = connection.prepareStatement(statementString);
			statement.setInt(1, personalData.getProvider_id());
			statement.executeQuery();
			ResultSet resultSet = statement.executeQuery();
			List<Availability> result = new LinkedList<Availability>();
			while (resultSet.next()) {
				Availability availability = new Availability();
				Integer id;
				Date date;
				id = new Integer(resultSet.getInt("AV_ID"));
				if (!resultSet.wasNull()) {
					availability.setAvailability_id(id);
				}
				id = new Integer(resultSet.getInt("P_ID"));
				if (!resultSet.wasNull()) {
					availability.setProvider_id(id);
				}
				date = resultSet.getDate("AV_START");
				if (!resultSet.wasNull()) {
					availability.setStart(date);
				}
				date = resultSet.getDate("AV_END");
				if (!resultSet.wasNull()) {
					availability.setEnd(date);
				}
				result.add(availability);
			}
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			if (statement != null) {
				statement.close();
			}
			Connector.closeConnection(connection);
		}
	}

}
